package dut.ir.lxb.weibo.db;

import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import dut.ir.lxb.weibo.bean.CacheBean;

public class CacheInfoDao 
{
	public static String tableName = "T_Cache";
	
	private static CacheInfoDao cacheDao;
	
	private CacheInfoDao()
	{
		
	}
	
	/**
	 * 单例模式获得一个数据库操作实例
	 * @return 实例
	 */
	public static CacheInfoDao getInstance()
	{
		if(cacheDao == null)
			return new CacheInfoDao();
		return cacheDao;
	}
	
	/**
	 * 判断是否缓存了某用户的数据
	 * @param uid 用户ID
	 * @return
	 */
	private boolean hasUser(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select * from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			boolean flag = rs.next();
			rs.close();
			ps.close();
			
			return flag;
			
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
	}
	
	/**
	 * 插入情感值，影响力，声誉度等等
	 * @param cb
	 * @return
	 */
	private boolean insertEmotionValue(CacheBean cb)
	{
		if(cb == null || cb.getUid() == null || cb.getUid().equals(""))
			return false;
		if(hasEmotionValue(cb.getUid()))
			updateEmotionValue(cb);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into %s (uid,emotionvalue,influencevalue,reputationvalue,lupdatetime) values (?,?,?,?,?)";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp ts = new Timestamp(new Date().getTime());
			
			ps.setString(1, cb.getUid());
			ps.setFloat(2, cb.getEmotionValue());
			ps.setFloat(3, cb.getInfluenceValue());
			ps.setFloat(4, cb.getReputationValue());
			ps.setTimestamp(5, ts);
			
			int result = ps.executeUpdate();
			ps.close();
			if(result > 0)
				return true;
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
		
	}
	
	/**
	 * 插入粉丝质量
	 * @param uid
	 * @param fansQuality
	 * @return
	 */
	private boolean insertFansQuality(String uid, String fansQuality)
	{
		if(uid == null || uid.equals(""))
			return false;
		if(hasFansQuality(uid))
			updateFansQualityValue(uid, fansQuality);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into %s (uid,fansquality,dupdatetime) values (?,?,?)";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp date = new Timestamp(new Date().getTime());
			
			ps.setString(1, uid);
			ps.setString(2, fansQuality);
			ps.setTimestamp(3, date);
			
			int result = ps.executeUpdate();
			ps.close();
			if(result > 0)
				return true;
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
		
	}
	
	/**
	 * 插入大五人格及爱好值
	 * @param cb
	 * @return 插入是否成功
	 */
	private boolean insertPersonalityValue(CacheBean cb)
	{
		if(cb == null || cb.getUid() == null || cb.getUid().equals(""))
			return false;
		if(hasPersonalityValue(cb.getUid()))
			updatePersonalityValue(cb);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into %s (uid,extraversion,neuroticism,openness,agreeableness,conscientiousness,hobby,zupdatetime) values (?,?,?,?,?,?,?,?)";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp ts = new Timestamp(new Date().getTime());
			
			
			ps.setString(1, cb.getUid());
			ps.setFloat(2, cb.getExtraversion());
			ps.setFloat(3, cb.getNeuroticism());
			ps.setFloat(4, cb.getOpenness());
			ps.setFloat(5, cb.getAgreeableness());
			ps.setFloat(6, cb.getConsicientiousness());
			ps.setString(7, cb.getHobby());
			ps.setTimestamp(8, ts);
			
			int result = ps.executeUpdate();
			ps.close();
			if(result > 0)
				return true;
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
		
	}
	
	
	
	
	
	/**
	 * 判断数据库中是否缓存了情感值(包括影响力，声誉度等)
	 * @param uid
	 * @return
	 */
	public boolean hasEmotionValue(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select lupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			boolean flag = false;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				Date date = rs.getTimestamp(1);
				if(date != null && !date.toString().equals(""))
					flag = true;
			}
			
			rs.close();
			ps.close();
			
			return flag;
			
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
	}
	
	
	/**
	 * 判断是否缓存了粉丝质量
	 * @param uid 用户ID
	 * @return
	 */
	public boolean hasFansQuality(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select dupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			boolean flag = false;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				Date date = rs.getTimestamp(1);
				if(date != null && !date.toString().equals(""))
					flag = true;
			}
			rs.close();
			ps.close();
			
			return flag;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
	}
	
	/**
	 * 判断数据库是否缓存了五大人格值
	 * @return
	 */
	public boolean hasPersonalityValue(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select zupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			boolean flag = false;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				Date date = rs.getTimestamp(1);
				if(date != null && !date.toString().equals(""))
					flag = true;
			}
			rs.close();
			ps.close();
			
			return flag;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
	}
	
	
	/**
	 * 获得情感值的更新时间
	 * @param uid 用户ID
	 * @return 
	 */
	public Date getEmotionUpdateTime(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select lupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			Date date = null;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				date = rs.getTimestamp(1);
			}
			rs.close();
			ps.close();
			return date;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
	}
	
	/**
	 * 获得粉丝质量上次更新时间
	 * @param uid
	 * @return
	 */
	public Date getFansQualityUpdateTime(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select dupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			Date date = null;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				date = rs.getTimestamp(1);
			}
			rs.close();
			ps.close();
			return date;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
	}
	
	/**
	 * 获得大五人格上次更新时间
	 * @param uid
	 * @return
	 */
	public Date getPersonalityUpdateTime(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select zupdatetime from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			Date date = null;
			if(rs.next())
			{
				//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				date = rs.getTimestamp(1);
			}
			rs.close();
			ps.close();
			
			return date;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
	}
	
	/**
	 * 更新情感值，包括情感值、声誉度、影响力等
	 * @param cb
	 * @return 更新是否成功
	 */
	public boolean updateEmotionValue(CacheBean cb)
	{
		if(!hasUser(cb.getUid()))
			insertEmotionValue(cb);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "update %s set emotionvalue=?,influencevalue=?,reputationvalue=?,lupdatetime=? where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp ts = new Timestamp(new Date().getTime());
			
			ps.setFloat(1, cb.getEmotionValue());
			ps.setFloat(2, cb.getInfluenceValue());
			ps.setFloat(3, cb.getReputationValue());
			ps.setTimestamp(4, ts);
			ps.setString(5, cb.getUid());
			
			int result = ps.executeUpdate();
			ps.close();
			
			if(result > 0)
				return true;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return false;
	}
	
	/**
	 * 更新粉丝质量
	 * @param uid
	 * @param fansQuality
	 * @return 更新是否成功
	 */
	public boolean updateFansQualityValue(String uid, String fansQuality)
	{
		if(!hasUser(uid))
			insertFansQuality(uid, fansQuality);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "update %s set fansquality=?,dupdatetime=? where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp ts = new Timestamp(new Date().getTime());
			
			ps.setString(1, fansQuality);
			ps.setTimestamp(2, ts);
			
			int result = ps.executeUpdate();
			ps.close();
			if(result > 0)
				return true;
			
		}
		catch (Exception e)
		{
			
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		return false;
		
		
	}
	
	/**
	 * 更新大五人格值，包括大五人格及Hobby
	 * @param cb
	 * @return 更新是否成功
	 */
	public boolean updatePersonalityValue(CacheBean cb)
	{
		if(!hasUser(cb.getUid()))
			insertPersonalityValue(cb);
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "update %s set extraversion=?,neuroticism=?,openness=?,agreeableness=?,conscientiousness=?,hobby=?,zupdatetime=? where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			Timestamp ts = new Timestamp(new Date().getTime());
			
			ps.setFloat(1, cb.getExtraversion());
			ps.setFloat(2, cb.getNeuroticism());
			ps.setFloat(3, cb.getOpenness());
			ps.setFloat(4, cb.getAgreeableness());
			ps.setFloat(5, cb.getConsicientiousness());
			ps.setString(6, cb.getHobby());
			ps.setTimestamp(7, ts);
			ps.setString(8, cb.getUid());
			
			int result = ps.executeUpdate();
			ps.close();
			if(result > 0)
				return true;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return false;
	}
	
	/**
	 * 从数据库中读取缓存情感值
	 * @param uid
	 * @return
	 */
	public CacheBean getEmotionValue(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select emotionvalue,influencevalue,reputationvalue from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			CacheBean cb = null;
			ResultSet rs = ps.executeQuery();
			if(rs.next())
			{
				cb = new CacheBean();
				cb.setEmotionValue(rs.getFloat(1));
				cb.setInfluenceValue(rs.getFloat(2));
				cb.setReputationValue(rs.getFloat(3));
				cb.setUid(uid);
			}
			rs.close();
			ps.close();
			return cb;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
		
	}
	
	/**
	 * 从数据库读取缓存粉丝质量
	 * @param uid
	 * @return 粉丝质量
	 */
	public String getFansQuality(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select fansquality from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			String fansQuality = null;
			if(rs.next())
			{
				fansQuality = rs.getString(1);
				
			}
			rs.close();
			ps.close();
			
			return fansQuality;
		}
		catch (Exception e)
		{
			
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
		
	}
	
	/**
	 * 从数据库读取缓存大五人格及Hobby值
	 * @param uid
	 * @return
	 */
	public CacheBean getPersonalityValue(String uid)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select extraversion,neuroticism,openness,agreeableness,conscientiousness,hobby from %s where uid=?";
			sqlStr = String.format(sqlStr, tableName);
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			ps.setString(1, uid);
			
			ResultSet rs = ps.executeQuery();
			CacheBean cb = null;
			if(rs.next())
			{
				cb = new CacheBean();
				cb.setExtraversion(rs.getFloat(1));
				cb.setNeuroticism(rs.getFloat(2));
				cb.setOpenness(rs.getFloat(3));
				cb.setAgreeableness(rs.getFloat(4));
				cb.setConsicientiousness(rs.getFloat(5));
				cb.setHobby(rs.getString(6));
				cb.setUid(uid);
			}
			rs.close();
			ps.close();
			return cb;
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (Exception e)
		{
			
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		return null;
		
	}
	
	
	
	
	
	
}
